/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package utopiaintelpackage.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import main.CommonMethods;

import org.apache.log4j.Logger;

import utopiaintelpackage.sot.SoT;
import botobjects.Province;
import database.DBConnectionFactory;

/**
 * A class that saves and retreives SoT objects
 * 
 * 
 */
public class SoTtoDB {
   /**
    * If the SoT shows that the province has a dragon, this method can be used to update the dragon status for the whole kd
    * 
    * @param kd
    * @param dragon
    */
   private static void addDragon(final String kd, final String dragon) {
	  PreparedStatement ps = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("UPDATE kd SET dragon = ? WHERE loc = ?");
		 ps.setString(1, dragon);
		 ps.setString(2, kd);
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   /**
    * If a province doesn't exist yet, it needs be added
    * 
    * @param som
    */
   private static void addProvince(final SoT sot) {
	  PreparedStatement ps = null;
	  try {
		 if (!CommonMethods.kdExists(sot.getKdloc())) {
			CommonMethods.addDefaultKD(sot.getKdloc());
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO province VALUES (?,?,?,?,?,?,?,?,?,false,?)");
		 ps.setString(1, sot.getName());
		 ps.setString(2, sot.getRace());
		 ps.setString(3, sot.getRulerName());
		 ps.setString(4, sot.getPersonality());
		 ps.setString(5, sot.getTitle());
		 ps.setString(6, sot.getKdloc());
		 ps.setInt(7, sot.getLand());
		 ps.setInt(8, sot.getNw());
		 ps.setInt(9, 0);
		 ps.setTimestamp(10, new Timestamp(sot.getUpdated().getTime()));
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   /**
    * Finds out if the kd the province is in has a dragon
    * 
    * @param kd
    * @return
    */
   private static String getDragon(final String kd) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM kd WHERE loc = ?");
		 ps.setString(1, kd);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("dragon");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   public static SoT getSoT(final String name) {
	  SoT sot = null;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM sot WHERE name = ?");
		 ps.setString(1, name);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			sot = new SoT();
			sot.setName(rs.getString("name"));
			sot.setMoney(rs.getInt("money"));
			sot.setFood(rs.getInt("food"));
			sot.setRunes(rs.getInt("runes"));
			sot.setPeasants(rs.getInt("peasants"));
			sot.setBe(rs.getString("be"));
			sot.setSoldiers(rs.getInt("soldiers"));
			sot.setOspecs(rs.getInt("ospecs"));
			sot.setDspecs(rs.getInt("dspecs"));
			sot.setElites(rs.getInt("elites"));
			sot.setHorses(rs.getInt("horses"));
			sot.setPrisoners(rs.getInt("prisoners"));
			sot.setMo(rs.getInt("mo"));
			sot.setMd(rs.getInt("md"));
			sot.setWizards(rs.getInt("wizards"));
			sot.setTradeBalance(rs.getInt("tradebalance"));
			sot.setHit(rs.getString("hit"));
			sot.setPlague(rs.getBoolean("plague"));
			sot.setUpdated(new Date(rs.getTimestamp("updated").getTime()));
			sot.setSavedBy(rs.getString("savedby"));
			sot.setExportLine(rs.getString("export"));

			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("SELECT * FROM province WHERE name = ?");
			ps.setString(1, sot.getName());
			rs = ps.executeQuery();

			if (rs.next()) {
			   sot.setLand(rs.getInt("land"));
			   sot.setNw(rs.getInt("nw"));
			   sot.setRace(rs.getString("race"));
			   sot.setPersonality(rs.getString("personality"));
			   sot.setTitle(rs.getString("title"));
			   sot.setKdloc(rs.getString("kdloc"));
			   sot.setDragon(getDragon(rs.getString("kdloc")));
			   sot.setThieves(rs.getInt("thieves"));
			   sot.setInfiltrated(rs.getBoolean("infiltrated"));
			   sot.setInfiltratedTime(rs.getTimestamp("infiltratedtime").getTime());
			}
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return sot;
   }

   public static String saveSoT(final SoT sot) {
	  if (!"".equals(CommonMethods.provinceExists(sot.getName()))) {
		 updateProvince(sot);
	  } else {
		 addProvince(sot);
	  }
	  addDragon(sot.getKdloc(), sot.getDragon());
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM sot WHERE name = ? AND updated > ?");
		 ps.setString(1, sot.getName());
		 ps.setTimestamp(2, new Timestamp(sot.getUpdated().getTime()));
		 rs = ps.executeQuery();

		 if (!rs.next()) {
			ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("DELETE FROM sot WHERE name = ?");
			ps.setString(1, sot.getName());
			ps.executeUpdate();

			try {
			   ps.close();
			} catch (final Exception e) {
			}

			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("INSERT INTO sot VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			ps.setString(1, sot.getName());
			ps.setInt(2, sot.getMoney());
			ps.setInt(3, sot.getFood());
			ps.setInt(4, sot.getRunes());
			ps.setInt(5, sot.getPeasants());
			ps.setString(6, sot.getBe());
			ps.setInt(7, sot.getSoldiers());
			ps.setInt(8, sot.getOspecs());
			ps.setInt(9, sot.getDspecs());
			ps.setInt(10, sot.getElites());
			ps.setInt(11, sot.getHorses());
			ps.setInt(12, sot.getPrisoners());
			ps.setInt(13, sot.getMo());
			ps.setInt(14, sot.getMd());
			ps.setInt(15, sot.getWizards());
			ps.setInt(16, sot.getTradeBalance());
			ps.setString(17, sot.getHit());
			ps.setBoolean(18, sot.getPlague());
			ps.setTimestamp(19, new Timestamp(sot.getUpdated().getTime()));
			ps.setString(20, sot.getExportLine());
			ps.setString(21, sot.getSavedBy());
			ps.executeUpdate();

			if (sot.getThieves() != 0) {
			   Province.updateThieves(sot.getName(), sot.getThieves());
			}
		 }
		 out = "SoT added";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Updates the province with information from the SoM
    * 
    * @param som
    */
   private static void updateProvince(final SoT sot) {
	  PreparedStatement ps = null;
	  try {
		 ps = DBConnectionFactory
			   .getConnectionHandler()
			   .getConnection()
			   .prepareStatement(
					 "UPDATE province SET " + "land = ?, nw = ?, title = ?, kdloc = ?, personality = ?, "
						   + "rulername = ?, race = ? WHERE name = ?");
		 ps.setInt(1, sot.getLand());
		 ps.setInt(2, sot.getNw());
		 ps.setString(3, sot.getTitle());
		 ps.setString(4, sot.getKdloc());
		 ps.setString(5, sot.getPersonality());
		 ps.setString(6, sot.getRulerName());
		 ps.setString(7, sot.getRace());
		 ps.setString(8, sot.getName());
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }
}
